package com.ht.api.controller;

import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.ht.api.bean.AjaxResult;
import com.ht.api.bean.LoginBean;
import com.ht.api.bean.UserInfo;
import com.ht.api.call.ICall;
import com.ht.api.db.Db;
import com.ht.api.db.DbList;
import com.ht.api.db.SqlParser;
import com.ht.api.db.exception.HtException;
import com.ht.api.db.util.RedisUtil;
import com.ht.api.util.UserUtil;
import com.mysql.cj.log.Log;

import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;

/**
 * 通用报表查询
 * @author asq
 * @createTime 2025年1月3日 12:03:59
 */
@RestController
@RequestMapping("/report")
public class ReportController {
	/**
	 * 通用报表查询（使用租户数据源）
	 * @param id 报表main_id
	 * @param body 接口参数
	 * @return
	 */
	@PostMapping("/{id}")
	public AjaxResult executeByTenant(@PathVariable String id, @RequestBody JSONObject body) {
		// 获取登录用户
		UserInfo loginUser = UserUtil.getLoginUser();
		long loginId = loginUser.getId();
		long tenantId = loginUser.getTenantId();
		
		return doExecute(LoginBean.createByTenant(loginId, tenantId), id, body);
	}

	/**
	 * 通用报表查询（使用系统数据源）
	 * @param id 报表main_id
	 * @param body 接口参数
	 * @return
	 */
	@PostMapping("/sys/{id}")
	public AjaxResult executeBySys(@PathVariable String id, @RequestBody JSONObject body) {
		// 获取登录用户
		UserInfo loginUser = UserUtil.getLoginUser();
		long loginId = loginUser.getId();
		
		return doExecute(LoginBean.createBySys(loginId), id, body);
	}
	
	/**
	 * 通用报表查询
	 * @param me 操作人
	 * @param id 报表main_id
	 * @param body 接口参数
	 * @return
	 */
	private AjaxResult doExecute(LoginBean me, String id, JSONObject body) {
		// 验证参数
		checkParam(body);
		
		// 查找id对应的sql
		String key = "report:" + id;
		String config = RedisUtil.get(key);
		if (StrUtil.isBlank(config)) {
			throw new HtException("通用报表不存在或未配置，id:" + id);
		}
		
		// 根据配置解析出sql语句
		String sql = parseToSql(config);
		
		// 根据前端参数处理sql并执行
		sql = replaceParam(sql, body);// 替换前端参数
		sql = filterParam(sql, body);// 去掉前端未传的参数语句
		
		// 执行最后剩下的sql
		DbList list = Db.query(me.getDataSource(), sql);
		return AjaxResult.success(list);
	}
	
	public static void main(String[] args) {
		String config = """
				{
					"count": true,
			        "select": [
						{
							"table": "order_item",
							"field": "total_price",
							"func": "max",
							"alias": "maxPrice"
						}
					],
					"form": "order_item",
					"join": [
						{
							"table1": "table1",
							"table2": "table2",
							"on1": "field1",
							"on2": "field2"
						},
						{
							"table1": "table2",
							"table2": "table3",
							"on1": "field2",
							"on2": "field3"
						}
					],
			        "where": [
			            {
							"table": "order_item",
			                "field": "score",
							"func": "max",
			                "operator": ">",
			                "value": "{value}"
			            }
			        ],
			        "group": [
						{
							"table": "order_item",
							"field": "total_price",
							"func": "max",
							"alias": "maxPrice"
						}
					],
			        "having": [
			            {
							"table": "order_item",
			                "field": "sum_score",
							"func": "max",
			                "operator": ">",
			                "value": "{value}"
			            }
			        ],
			        "order": [
			            {
							"table": "order_item",
			                "field": "sum_score",
							"func": "max",
			                "sort": "desc"
			            }
			        ]
			    }
				""";
		String str = parseToSql(config);
		System.err.println(str);
	}

	/**
	 * 解析报表配置为sql语句
	 * @param configStr 报表的配置json
	 * @return
	 */
	public static String parseToSql(String configStr) {
		// 获取配置信息
		JSONObject config = JSONUtil.parseObj(configStr);
		boolean countField = config.getBool("count");
		JSONArray select = config.getJSONArray("select");
		String from = config.getStr("from");
		JSONArray join = config.getJSONArray("join");
		JSONArray where = config.getJSONArray("where");
		JSONArray group = config.getJSONArray("group");
		JSONArray having = config.getJSONArray("having");
		JSONArray order = config.getJSONArray("order");
		
		// 解析为SqlParser对象
		SqlParser sql = Db.createSqlParser(from);
		
		// 如果有join
		if (join != null) {
			// `order` inner join `order_item` on `order`.order_id=`order_item`.order_id
			StringBuilder joinBuilder = new StringBuilder(); 
			for (int i = 0; i < join.size(); i++) {
				JSONObject joinObj = join.getJSONObject(i);
				String joinStr = String.format(" inner join `%s` on `%s`.%s=`%s`.%s", 
						joinObj.getStr("table2"), 
						joinObj.getStr("table1"), joinObj.getStr("on1"), 
						joinObj.getStr("table2"), joinObj.getStr("on2")
						);
				joinBuilder.append(joinStr);
			}
			sql.setTable(joinBuilder.toString());
		}
		
		// select
		StringBuilder selectBuilder = new StringBuilder(); 
		if (group != null) {
			selectBuilder.append(getSelectSql(group, true));
		} else {
			throw new HtException("报表配置不正确，缺少group字段~");
		}
		if (countField) {
			selectBuilder.append("count(1)c,");
		}
		if (select != null) {
			selectBuilder.append(getSelectSql(select, true));
		}
		selectBuilder.deleteCharAt(selectBuilder.length() - 1);
		sql.select(selectBuilder.toString());
		
		// where
		if (where != null) {
			sql.where("1", "=", 1);
			for (int i = 0; i < where.size(); i++) {
				JSONObject whereObj = where.getJSONObject(i);
				String func = whereObj.getStr("func");
				String table = whereObj.getStr("table");
				String field = whereObj.getStr("field");
				String operator = whereObj.getStr("operator");
				String value = whereObj.getStr("value");
				boolean isVar = value.contains("{");
				String left = isVar ? "[" : "";
				String right = isVar ? "]" : "";
				value = value + right;
				
				if (StrUtil.isNotBlank(func)) {
					sql.where(String.format("%s%s(`%s`.`%s`)", left, func, table, field), operator, value);
				} else {
					sql.where(String.format("%s`%s`.`%s`", left, table, field), operator, value);
				}
			}
		}
		
		// group
		if (group != null) {
			String groupStr = getSelectSql(group, false);
			sql.group(groupStr.substring(0, groupStr.length() - 1));
		}
		
		// having
		if (having != null) {
			sql.having("1", "=", 1);
			for (int i = 0; i < having.size(); i++) {
				JSONObject havingObj = where.getJSONObject(i);
				String func = havingObj.getStr("func");
				String table = havingObj.getStr("table");
				String field = havingObj.getStr("field");
				String operator = havingObj.getStr("operator");
				String value = havingObj.getStr("value");
				boolean isVar = value.contains("{");
				String left = isVar ? "[" : "";
				String right = isVar ? "]" : "";
				value = value + right;
				
				if (StrUtil.isNotBlank(func)) {
					sql.having(String.format("%s%s(`%s`.`%s`)", left, func, table, field), operator, value);
				} else {
					sql.having(String.format("%s`%s`.`%s`", left, table, field), operator, value);
				}
			}
		}
		
		// order
		if (order != null) {
			for (int i = 0; i < order.size(); i++) {
				JSONObject orderObj = where.getJSONObject(i);
				String func = orderObj.getStr("func");
				String table = orderObj.getStr("table");
				String field = orderObj.getStr("field");
				String sort = orderObj.getStr("sort");
				
				if (StrUtil.isNotBlank(func)) {
					sql.order(String.format("%s(`%s`.`%s`)", func, table, field), sort);
				} else {
					sql.order(String.format("`%s`.`%s`", table, field), sort);
				}
			}
		}
		
		System.err.println(sql.toStringPretty());
		
		return sql.getOriginalSql();
	}
	
	/**
	 * 获取配置中的select部分或者group部分的sql
	 * @param array config配置中的select/group数组
	 * @param appendAlias 是否需要添加上别名
	 * @return
	 */
	private static String getSelectSql(JSONArray array, boolean appendAlias) {
		StringBuilder selectBuilder = new StringBuilder(); 
		for (int i = 0; i < array.size(); i++) {
			JSONObject selectObj = array.getJSONObject(i);
			String func = selectObj.getStr("func");
			String table = selectObj.getStr("table");
			String field = selectObj.getStr("field");
			String alias = selectObj.getStr("alias");
			
			String str = null;
			if (StrUtil.isNotBlank(func)) {
				str = String.format("%s(`%s`.`%s`)", func, table, field);
			} else {
				str = String.format("`%s`.`%s`", table, field);
			}
			
			if (appendAlias) {
				str += " " + alias;
			}
			str += ",";
			
			selectBuilder.append(str);
		}
		return selectBuilder.toString();
	}

	/**
	 * 验证参数
	 * @param body 参数对象
	 */
	private void checkParam(JSONObject body) {
		// 要求参数必须要有param字段
		if (!body.containsKey(ICall.PARAM)) {
			throw new HtException(String.format("json参数需要%s字段~", ICall.PARAM));
		}
		Object param = body.get(ICall.PARAM);
		if (!(param instanceof JSONArray)) {
			throw new HtException(String.format("%s字段必须为JSONArray类型~", ICall.PARAM));
		}
	}
	
	/**
	 * 将sql语句中的{}替换为前端参数值
	 * @param sql
	 * @param body
	 * @return
	 */
	private static String replaceParam(String sql, JSONObject body) {
	    JSONArray paramArr = body.getJSONArray("param");
	    if (paramArr == null || paramArr.isEmpty()) {
	        return sql;
	    }

	    // 将参数转为Map方便查找
	    Map<String, Object> paramMap = new HashMap<>();
	    for (int i = 0; i < paramArr.size(); i++) {
	        JSONObject param = paramArr.getJSONObject(i);
	        paramMap.put(param.getStr("key"), param.get("value"));
	    }

	    // 使用正则表达式匹配所有{变量}并直接替换
	    Pattern pattern = Pattern.compile("\\{(.+?)\\}");
	    Matcher matcher = pattern.matcher(sql);
	    StringBuffer sb = new StringBuffer();

	    while (matcher.find()) {
	        String paramName = matcher.group(1);
	        Object paramValue = paramMap.get(paramName);
	        // 直接替换为参数值（不区分类型，不做任何处理）
	        matcher.appendReplacement(sb, paramValue != null ? paramValue.toString() : matcher.group());
	    }
	    matcher.appendTail(sb);
	    return sb.toString();
	}

	/**
	 * 将sql语句中的[]过滤掉（如果前端没有传相应值的时候）
	 * @param sql
	 * @param body
	 * @return
	 */
	private static String filterParam(String sql, JSONObject body) {
	    JSONArray paramArr = body.getJSONArray("param");
	    if (paramArr == null || paramArr.isEmpty()) {
	        // 如果没有参数，移除所有中括号内容
	        return sql.replaceAll("\\[.*?\\]", "");
	    }

	    // 收集所有前端传递的参数key
	    Map<String, Boolean> paramKeys = new HashMap<>();
	    for (int i = 0; i < paramArr.size(); i++) {
	        JSONObject param = paramArr.getJSONObject(i);
	        paramKeys.put(param.getStr("key"), true);
	    }

	    // 使用正则表达式匹配所有[条件语句]
	    Pattern pattern = Pattern.compile("\\[(.*?)\\]");
	    Matcher matcher = pattern.matcher(sql);
	    StringBuffer sb = new StringBuffer();

	    while (matcher.find()) {
	        String condition = matcher.group(1);
	        // 检查条件语句中是否包含前端没有传递的参数
	        boolean shouldKeep = true;
	        
	        // 提取条件语句中的所有参数名
	        Pattern paramPattern = Pattern.compile("\\{(.+?)\\}");
	        Matcher paramMatcher = paramPattern.matcher(condition);
	        
	        while (paramMatcher.find()) {
	            String paramName = paramMatcher.group(1);
	            if (!paramKeys.containsKey(paramName)) {
	                shouldKeep = false;
	                break;
	            }
	        }
	        
	        if (shouldKeep) {
	            // 保留这个条件语句
	            matcher.appendReplacement(sb, condition);
	        } else {
	            // 移除这个条件语句
	            matcher.appendReplacement(sb, "");
	        }
	    }
	    matcher.appendTail(sb);
	    return sb.toString();
	}
}
